/*DONE* Table [dbo].[<CompanyName>$Member Club$5ecfc871-5d82-43f1-9c54-59685e82318d]  1/1  Script Date: 28.7.2020 - JI ******/
/*Add prestaging table for all companies in LSInsight$Companies table*/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

DECLARE @CreateTable as VARCHAR(MAX)
DECLARE @Company as VARCHAR(100)
DECLARE @CompanyCounter as INT
DECLARE @i as INT

SET @i = 0
SET @CompanyCounter = (SELECT MAX([Company]) FROM LSInsight$Companies)

WHILE @i <= @CompanyCounter 
BEGIN
SET @Company = (SELECT LSInsight$Companies.[CompanyPrefix] from dbo.LSInsight$Companies WHERE [Company] = @i)

IF @Company IS NOT NULL
BEGIN

DECLARE @DropTable as VARCHAR(MAX)
SET @DropTable = 'DROP TABLE IF EXISTS [dbo].['+@Company+'$Member Club$5ecfc871-5d82-43f1-9c54-59685e82318d]'
EXEC(@DropTable)

SET @CreateTable = 'CREATE TABLE [dbo].['+@Company+'$Member Club$5ecfc871-5d82-43f1-9c54-59685e82318d](
	[timestamp] [timestamp] NOT NULL,
	[Code] [nvarchar](10) NOT NULL,
	[Description] [nvarchar](30) NOT NULL,
	[Point Calculation] [tinyint] NOT NULL,
	[Club Type] [int] NOT NULL,
	[Card Registration] [int] NOT NULL,
	[Starting Card No_] [nvarchar](100) NOT NULL,
	[Card No_ Length] [int] NOT NULL,
	[Barcode Mask] [nvarchar](22) NOT NULL,
	[Contact Handling] [int] NOT NULL,
	[Default Scheme] [nvarchar](10) NOT NULL,
	[Unassigned Account No_] [nvarchar](20) NOT NULL,
	[Show Points on Receipt] [int] NOT NULL,
	[Display Message on POS] [int] NOT NULL,
	[Default Cust_ Disc_ Group] [nvarchar](10) NOT NULL,
	[Member Point Tender Type] [nvarchar](10) NOT NULL,
	[Default Price Group] [nvarchar](10) NOT NULL,
	[Card Expiration] [varchar](32) NOT NULL,
	[Unassigned Card Exp_] [varchar](32) NOT NULL,
	[Point Expiration] [varchar](32) NOT NULL,
	[Point Rounding Type] [int] NOT NULL,
	[Point Rounding Precision] [decimal](38, 20) NOT NULL,
	[Point Value] [decimal](38, 20) NOT NULL,
	[Period Calc for Up-_Downgrade] [varchar](32) NOT NULL,
	[Min_ Trans_Amt for Point Calc_] [decimal](38, 20) NOT NULL,
	[Transferred Point Type] [int] NOT NULL,
	[Transferred Point Expir_] [varchar](32) NOT NULL,
	[Min Remain_ Period for Trans_] [varchar](32) NOT NULL,
	[Transfer Value Factor] [decimal](38, 20) NOT NULL,
	[Disc_ Benefit Point Type] [int] NOT NULL,
	[Reason Codes Accounts] [nvarchar](10) NOT NULL,
	[Reason Codes Contacts] [nvarchar](10) NOT NULL,
	[Reason Codes Cards] [nvarchar](10) NOT NULL,
	[Reason Codes Point Journals] [nvarchar](10) NOT NULL,
	[Account No_ Series] [nvarchar](10) NOT NULL,
	[Card Setup Type] [int] NOT NULL,
	[Card No_ Series] [nvarchar](10) NOT NULL,
	[Contact No_ Series] [nvarchar](10) NOT NULL,
	[Post to G_L] [tinyint] NOT NULL,
	[Account No_] [nvarchar](20) NOT NULL,
	[Bal_ Account No_] [nvarchar](20) NOT NULL,
	[Expiration Period Type] [int] NOT NULL,
	[Show FBP Coupons on Receipt] [int] NOT NULL,
	[Club E-Mail] [nvarchar](80) NOT NULL,
	[$systemId] [uniqueidentifier] NOT NULL,
	[Void Member Point Apply Method] [int] NOT NULL,
 CONSTRAINT ['+@Company+'$Member Club$5ecfc871-5d82-43f1-9c54-59685e82318d$Key1] PRIMARY KEY CLUSTERED 
(
	[Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT ['+@Company+'$Member Club$5ecfc871-5d82-43f1-9c54-59685e82318d$$systemId] UNIQUE NONCLUSTERED 
(
	[$systemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]'

EXEC(@CreateTable)

END
SET @i = @i+1
END